package com.robusta.cityuate.services;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
//This class handle all DB interaction
public class DBClass {
	
//List of all DB Tables
	public final static String TABLE_NAME_REMINDER = "Reminder";
//Create Tables (each table is hardwired)
	public static void CreateTables(Context c) {
		SQLiteDatabase db = null;
		try {
			db = c.openOrCreateDatabase("UserDB",
					SQLiteDatabase.OPEN_READWRITE, null);
			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ "Reminder"
					+ " (Id INTEGER PRIMARY KEY,_Address VARCHAR, _Long VARCHAR,_Lat VARCHAR, _Comment VARCHAR"
					+ ")");
			// add more tables here.
		} catch (Exception e) {
			System.out.println("DB Failure");
		} finally {
			if (db != null)
				db.close();
		}

	}
//A Method for insertion data into database
	public static void InsertIntoTable(Context c, String table, String[] values) {
		SQLiteDatabase db = null;
		try {
			db = c.openOrCreateDatabase("UserDB",
					SQLiteDatabase.OPEN_READWRITE, null);
			db.execSQL("INSERT INTO " + table + " VALUES "
					+ convertArrayToString(values));
		} catch (Exception e) {
			System.out.println("DB Failure");
		} finally {
			if (db != null)
				db.close();
		}

	}
//A method for updating a row into table with new values
	public static void UpdateTable(Context c, String table, String values,
			String id) {
		SQLiteDatabase db = null;
		try {
			db = c.openOrCreateDatabase("UserDB",
					SQLiteDatabase.OPEN_READWRITE, null);
			db.execSQL("UPDATE" + table + "SET" + values + "WHERE Id = " + id);
		} catch (Exception e) {
			System.out.println("DB Failure");
		} finally {
			if (db != null)
				db.close();
		}

	}
//get the whole table
	public static ArrayList<ArrayList<String>> getTable(Context c, String table) {
		SQLiteDatabase db = null;
		ArrayList<ArrayList<String>> retTable = new ArrayList<ArrayList<String>>();
		try {
			db = c.openOrCreateDatabase("UserDB",
					SQLiteDatabase.OPEN_READWRITE, null);
			String myDataQuery = "SELECT * FROM " + table;
			Cursor myDataCursor = db.rawQuery(myDataQuery, null);
			if (myDataCursor.moveToFirst()) {
				do {
					ArrayList<String> row = new ArrayList<String>();
					for (int i = 0; i < myDataCursor.getColumnCount(); i++) {
						if (myDataCursor.getColumnCount() > 0) {
							row.add(myDataCursor.getString(i));
						}
					}
					retTable.add(row);
				} while (myDataCursor.moveToNext());
			}
			myDataCursor.close();
			db.close();
		} catch (Exception e) {
			System.out.println("DB Failure");
		} finally {
			if (db != null)
				db.close();
		}
		return retTable;

	}
//get only one row from the table
	public static void getARowFromTable(Context c, String table, String id) {
		SQLiteDatabase db = null;
		try {
			db = c.openOrCreateDatabase("UserDB",
					SQLiteDatabase.OPEN_READWRITE, null);
			db.execSQL("SELECT * FROM" + table + "WHERE Id = " + id);
		} catch (Exception e) {
			System.out.println("DB Failure");
		} finally {
			if (db != null)
				db.close();
		}

	}
//Delete a row from table
	public static void DeleteFromTable(Context c, String table, String id) {
		SQLiteDatabase db = null;
		try {
			db = c.openOrCreateDatabase("UserDB",
					SQLiteDatabase.OPEN_READWRITE, null);
			db.execSQL("DELETE FROM " + table + " WHERE Id = " + id);
		} catch (Exception e) {
			System.out.println("DB Failure");
		} finally {
			if (db != null)
				db.close();
		}

	}
//a helper method to convert an array of strings into one SQL String
	public static String convertArrayToString(String[] array) {
		String str = "( null ,";
		for (int i = 0; i < array.length; i++) {
			str = str + "'" + array[i] + "'";
			if (i < array.length - 1) {
				str = str + ",";
			}
		}
		str = str + ")";
		return str;
	}
}
